import sqlite3
from typing import List, Dict, Any, Optional


class SimpleSQLite:
    def __init__(self, db_file: str = "app.db"):
        """初始化数据库连接

        Args:
            db_file: 数据库文件路径
        """
        self.db_file = db_file
        self.connection = None

    def __enter__(self):
        """支持 with 语句"""
        self.connect()
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        """退出 with 语句时自动关闭连接"""
        self.close()

    def connect(self):
        """连接数据库"""
        self.connection = sqlite3.connect(self.db_file)
        self.connection.row_factory = sqlite3.Row  # 使用Row对象，可以像字典一样访问

    def close(self):
        """关闭数据库连接"""
        if self.connection:
            self.connection.close()

    def execute(self, sql: str, params: tuple = (), commit: bool = True) -> sqlite3.Cursor:
        """执行SQL语句

        Args:
            sql: SQL语句
            params: 参数元组
            commit: 是否自动提交

        Returns:
            游标对象
        """
        cursor = self.connection.cursor()
        cursor.execute(sql, params)
        if commit:
            self.connection.commit()
        return cursor

    def fetch_all(self, sql: str, params: tuple = ()) -> List[Dict[str, Any]]:
        """查询所有记录

        Args:
            sql: SQL查询语句
            params: 参数元组

        Returns:
            字典列表形式的结果
        """
        cursor = self.execute(sql, params, commit=False)
        return [dict(row) for row in cursor.fetchall()]

    def fetch_one(self, sql: str, params: tuple = ()) -> Optional[Dict[str, Any]]:
        """查询单条记录

        Args:
            sql: SQL查询语句
            params: 参数元组

        Returns:
            单条字典形式的结果，如果没有结果返回None
        """
        cursor = self.execute(sql, params, commit=False)
        row = cursor.fetchone()
        return dict(row) if row else None

    def insert(self, table: str, data: Dict[str, Any]) -> int:
        """插入单条数据

        Args:
            table: 表名
            data: 数据字典 {列名: 值}

        Returns:
            插入的行ID
        """
        columns = ", ".join(data.keys())
        placeholders = ", ".join(["?"] * len(data))
        sql = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        cursor = self.execute(sql, tuple(data.values()))
        return cursor.lastrowid

    def update(self, table: str, data: Dict[str, Any], where: str, params: tuple = ()) -> int:
        """更新数据

        Args:
            table: 表名
            data: 要更新的数据字典 {列名: 新值}
            where: WHERE条件语句
            params: WHERE条件的参数

        Returns:
            受影响的行数
        """
        set_clause = ", ".join([f"{key} = ?" for key in data.keys()])
        sql = f"UPDATE {table} SET {set_clause} WHERE {where}"
        cursor = self.execute(sql, tuple(data.values()) + params)
        return cursor.rowcount

    def delete(self, table: str, where: str, params: tuple = ()) -> int:
        """删除数据

        Args:
            table: 表名
            where: WHERE条件语句
            params: WHERE条件的参数

        Returns:
            受影响的行数
        """
        sql = f"DELETE FROM {table} WHERE {where}"
        cursor = self.execute(sql, params)
        return cursor.rowcount
    